{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  Column1    Column2    Column3                                Column4  \\\n",
      "0  《冲上云霄》 2015-02-19 2015-03-29                             寰亚电影制作有限公司   \n",
      "1  《百团大战》 2015-08-28 2015-10-11           八一电影制片厂；中国电影股份有限公司；北京紫禁城影业公司   \n",
      "2  《浪漫天降》 2015-10-23 2015-11-08                                    NaN   \n",
      "3   《简单爱》 2015-07-03 2015-07-19             中视合利（北京）文化投资有限公司一鸣影业公司（美国）   \n",
      "4  《一念天堂》 2015-12-31 2016-02-13  天河盛宴，凯德盛世（北京）投资管理有限公司，和云筹(北京)网络科技有限公司   \n",
      "\n",
      "   Column5                  Column6   Column7      Column8 Column9  \n",
      "0  叶伟信，邹凯光  古天乐，郑秀文，吴镇宇，张智霖，佘诗曼，郭采洁     剧情，爱情  票房（万）1563.3      北京  \n",
      "1  宁海强，张玉中   陶泽如，刘之冰，印小天，吴越，唐国强，王伍福     战争/历史  票房（万）4137.3      天津  \n",
      "2       宁瀛                夏雨，关晓彤，邱泽  浪漫，爱情，喜剧    票房（万）75.2      广州  \n",
      "3      崔龄燕           许绍洋，张琳，谢雨芩，石铭熙  都市浪漫爱情喜剧   票房（万）232.7      成都  \n",
      "4       张承     沈腾，马丽，林雪，杜晓宇，王子子，李元鹏        喜剧   票房（万）829.5      沈阳  \n",
      "影片A的上映天数：3345天\n",
      "影片A的日平均票房：$1392.84\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# 加载数据\n",
    "df = pd.read_excel('C:\\\\Users\\\\Administrator\\\\Desktop\\\\movies数据集.xlsx')\n",
    "\n",
    "# 查看数据前几行\n",
    "print(df.head())\n",
    "\n",
    "# 清洗数据\n",
    "df.dropna(subset=['Column1', 'Column2', 'Column3', 'Column8'], inplace=True)\n",
    "\n",
    "# 计算每部电影的上映天数\n",
    "df['release_date'] = pd.to_datetime(df['Column2'])\n",
    "df['days_released'] = (pd.Timestamp('now') - df['release_date']).dt.days\n",
    "\n",
    "# 处理票房数据格式\n",
    "df['Column8'] = df['Column8'].str.replace('票房（万）', '').astype(float)\n",
    "\n",
    "# 计算日平均票房\n",
    "daily_avg_gross = df['Column8'].mean()\n",
    "\n",
    "# 输出影片A的上映天数和日平均票房\n",
    "movie_A_data = df[df['Column1'] == '《冲上云霄》']\n",
    "days_released_A = movie_A_data['days_released'].values[0]\n",
    "daily_avg_box_office_A = daily_avg_gross\n",
    "\n",
    "print(f\"影片A的上映天数：{days_released_A}天\")\n",
    "print(f\"影片A的日平均票房：${daily_avg_box_office_A:.2f}\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(6636, 7)\n",
      "Index(['id', 'description', 'tags', 'manufacturer', 'group', 'portions',\n",
      "       'nutrients'],\n",
      "      dtype='object')\n",
      "    value units                  description        group\n",
      "0   25.18     g                      Protein  Composition\n",
      "1   29.20     g            Total lipid (fat)  Composition\n",
      "2    3.06     g  Carbohydrate, by difference  Composition\n",
      "3    3.28     g                          Ash        Other\n",
      "4  376.00  kcal                       Energy       Energy\n",
      "group\n",
      "Baby Foods                           209\n",
      "Baked Products                       496\n",
      "Beef Products                        618\n",
      "Beverages                            278\n",
      "Breakfast Cereals                    403\n",
      "Cereal Grains and Pasta              183\n",
      "Dairy and Egg Products               107\n",
      "Ethnic Foods                         165\n",
      "Fast Foods                           365\n",
      "Fats and Oils                         97\n",
      "Finfish and Shellfish Products       255\n",
      "Fruits and Fruit Juices              328\n",
      "Lamb, Veal, and Game Products        345\n",
      "Legumes and Legume Products          365\n",
      "Meals, Entrees, and Sidedishes        57\n",
      "Nut and Seed Products                128\n",
      "Pork Products                        328\n",
      "Poultry Products                     116\n",
      "Restaurant Foods                      51\n",
      "Sausages and Luncheon Meats          111\n",
      "Snacks                               162\n",
      "Soups, Sauces, and Gravies           275\n",
      "Spices and Herbs                      41\n",
      "Sweets                               341\n",
      "Vegetables and Vegetable Products    812\n",
      "dtype: int64\n",
      "    value units                  description\n",
      "0   25.18     g                      Protein\n",
      "1   29.20     g            Total lipid (fat)\n",
      "2    3.06     g  Carbohydrate, by difference\n",
      "3    3.28     g                          Ash\n",
      "4  376.00  kcal                       Energy\n",
      "     id                       description_x                   group  \\\n",
      "0  1008                     Cheese, caraway  Dairy and Egg Products   \n",
      "1  1009                     Cheese, cheddar  Dairy and Egg Products   \n",
      "2  1018                        Cheese, edam  Dairy and Egg Products   \n",
      "3  1019                        Cheese, feta  Dairy and Egg Products   \n",
      "4  1028  Cheese, mozzarella, part skim milk  Dairy and Egg Products   \n",
      "\n",
      "  manufacturer   value units                description_y  \n",
      "0                25.18     g                      Protein  \n",
      "1                29.20     g            Total lipid (fat)  \n",
      "2                 3.06     g  Carbohydrate, by difference  \n",
      "3                 3.28     g                          Ash  \n",
      "4               376.00  kcal                       Energy  \n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# A. 导入USDA食物营养数据的JSON数据文件\n",
    "with open('C:\\\\Users\\\\Administrator\\\\Desktop\\\\foods-2011-10-03.json') as f:\n",
    "    data = json.load(f)\n",
    "\n",
    "# B. 查看食物营养数据集的记录数和字段名属性\n",
    "food_data = pd.DataFrame(data)  # 直接将所有数据转换为DataFrame\n",
    "print(food_data.shape)\n",
    "print(food_data.columns)\n",
    "\n",
    "# C. 查看食物营养数据集中营养成分（nutrients）所包含的信息\n",
    "nutrients_data = []\n",
    "for food in data:\n",
    "    for nutrient in food['nutrients']:\n",
    "        nutrients_data.append(nutrient)\n",
    "nutrients_df = pd.DataFrame(nutrients_data)\n",
    "print(nutrients_df.head())\n",
    "\n",
    "# D. 创建包含食物的名称、分类、编号、制造商等信息表，分析食物类别的分布情况\n",
    "food_info = pd.DataFrame([{'id': food['id'], 'description': food['description'], 'group': food['group'], 'manufacturer': food['manufacturer']} for food in data])\n",
    "food_info_grouped = food_info.groupby('group').size()\n",
    "print(food_info_grouped)\n",
    "\n",
    "# E. 创建食物营养数据集中的全部食物营养成分数据表，然后进行数据分析\n",
    "nutrients_info = nutrients_df[['value', 'units', 'description']]\n",
    "print(nutrients_info.head())\n",
    "\n",
    "# F. 对全部食物营养成分数据进行去除重复值操作\n",
    "nutrients_info_cleaned = nutrients_info.drop_duplicates()\n",
    "\n",
    "# G. 将包含食物的名称、分类、编号、制造商等信息表与食物营养成分数据表合并\n",
    "merged_data = pd.merge(food_info, nutrients_info_cleaned, left_index=True, right_index=True)\n",
    "\n",
    "# 输出合并后的数据\n",
    "print(merged_data.head())\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
